package org.mebusw.dao.impl;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.ObjectInputStream;
import java.io.ObjectOutputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;


import org.mebusw.dao.IGameTableDAO;
import org.mebusw.struts.vo.GameTable;

/**
 * mysql> desc games;
 * +------------+---------+------+-----+---------+-------+
 * | Field      | Type    | Null | Key | Default | Extra |
 * +------------+---------+------+-----+---------+-------+
 * | gameTable  | text    | YES  |     | NULL    |       |
 * | createDate | date    | YES  |     | NULL    |       |
 * | data       | blob    | YES  |     | NULL    |       |
 * | gid        | int(11) | NO   | PRI | NULL    |       |
 * +------------+---------+------+-----+---------+-------+
 * @author j1shen
 *
 */
public class IGameTableDAOImpl implements IGameTableDAO {
	private Connection conn = null;

	public IGameTableDAOImpl(Connection conn) {
		this.conn = conn;
	}

	public boolean doCreate(GameTable gameTable) throws Exception {
		boolean flag = false;
		PreparedStatement pstmt = null;
		String sql = "INSERT INTO games(data, gid) VALUES (?,?)";
		//TODO gid is auto-increment in database?
		try {
			pstmt = this.conn.prepareStatement(sql);

			ByteArrayOutputStream baos = new ByteArrayOutputStream();
			ObjectOutputStream oos = new ObjectOutputStream(baos);
			oos.writeObject(gameTable);
			ByteArrayInputStream bais = new ByteArrayInputStream(baos
					.toByteArray());

			pstmt.setBinaryStream(1, bais, bais.available());
			oos.close();
			pstmt.setInt(2, gameTable.getGid());

			int count = pstmt.executeUpdate();
			if (count > 0)
				flag = true;
		} catch (Exception e) {
			throw e;
		} finally {
			try {
				pstmt.close();
			} catch (Exception e) {

			}
		}
		return flag;
	}

	@Override
	public boolean doDelete(int gid) throws Exception {
		boolean flag = false;
		PreparedStatement pstmt = null;
		String sql = "DELETE FROM games WHERE gid=?";

		try {
			pstmt = this.conn.prepareStatement(sql);
			pstmt.setInt(1, gid);
			int count = pstmt.executeUpdate();
			if (count > 0)
				flag = true;
		} catch (Exception e) {
			throw e;
		} finally {
			try {
				pstmt.close();
			} catch (Exception e) {

			}
		}

		return flag;
	}

	@Override
	public boolean doUpdate(GameTable gameTable) throws Exception {
		boolean flag = false;
		PreparedStatement pstmt = null;
		String sql = "UPDATE games SET data=? WHERE gid=?";

		try {
			pstmt = this.conn.prepareStatement(sql);
			ByteArrayOutputStream baos = new ByteArrayOutputStream();
			ObjectOutputStream oos = new ObjectOutputStream(baos);
			oos.writeObject(gameTable);
			ByteArrayInputStream bais = new ByteArrayInputStream(baos
					.toByteArray());
			pstmt.setBinaryStream(1, bais, bais.available());
			oos.close();
			pstmt.setInt(2, gameTable.getGid());
			
			int count = pstmt.executeUpdate();
			if (count > 0)
				flag = true;
		} catch (Exception e) {
			throw e;
		} finally {
			try {
				pstmt.close();
			} catch (Exception e) {

			}
		}

		return flag;
	}


	@Override
	public GameTable findById(int gid) throws Exception {
		GameTable gameTable = null;
		PreparedStatement pstmt = null;
		String sql = "SELECT data FROM games WHERE gid=?";

		try {
			pstmt = this.conn.prepareStatement(sql);
			pstmt.setInt(1, gid);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {

				ObjectInputStream ois = new ObjectInputStream(rs
						.getBinaryStream(1));
				gameTable = (GameTable) ois.readObject();
			}
			rs.close();
		} catch (Exception e) {
			throw e;
		} finally {
			try {
				pstmt.close();
			} catch (Exception e) {
			}
		}
		return gameTable;
	}

	@Override
	public int getAllCount(String keyword) throws Exception {
		PreparedStatement pstmt = null;
		String sql;
		int count = 0;

		try {
			sql = "select COUNT(uid) from user "
					+ "where uid like upper(?) or username like upper(?) or score like upper(?) ";

			pstmt = this.conn.prepareStatement(sql);
			pstmt.setString(1, "%" + keyword + "%");
			pstmt.setString(2, "%" + keyword + "%");
			pstmt.setString(3, "%" + keyword + "%");

			ResultSet rs = pstmt.executeQuery();

			while (rs.next()) {
				count = rs.getInt(1);
			}
			rs.close();
		} catch (Exception e) {
			throw e;
		} finally {
			try {
				pstmt.close();
			} catch (Exception e) {
			}
		}
		return count;
	}

}
